let mysql = require('mysql'),
    pool = mysql.createPool({
        host:'localhost',
        user:'root',
        password:'root',
        database:'test'
    });


/**
 * @author: ares
 * @date: 2018/4/1 14:32
 * @description: 生成附加语句 (where order sort)
 * @param {array} where_clause     where条件,对象元素类型的数组,对象元素{field:'字段名',operator:'操作符',value:'条件值',and_or:'AND | OR'}
 * @param {object} order_clause    排序条件,{fields:[],sort:'ASC | DESC'}
 * @param {boolean} binary        是否区分大小写
 * @returns {string}
 */
function buildSubjoinClause(where_clause,order_clause,binary) {
    var clause = (typeof binary == 'boolean' && binary) ? ' WHERE BINARY' : ' WHERE',
        order = '';

    if (where_clause) {
        for (var i = 0;i < where_clause.length;i++){
            var temp_clause = '',
                field = where_clause[i].field,
                operator = where_clause[i].operator,
                val = where_clause[i].value,
                and_or = (typeof where_clause[i].and_or == 'undefined') ? '' : (' ' + where_clause[i].and_or + ' ');
            if (typeof val == 'string') {
                val = '"' + val + '"';
            }
            if(operator == 'LIKE' || operator == 'like'){
                temp_clause += (' ' + field + ' LIKE ' + val + and_or);
            }else{
                temp_clause += (' ' + field + operator + val + and_or);
            }
            clause += temp_clause;
        }
    }else {
        clause = '';
    }

    if (order_clause){
        var order_fields = order_clause.fields,
            order_sort = order_clause.sort;
        for (var j = 0;j < order_fields.length;j++){
            order += (',' + order_fields[j]);
        }
        if (order_sort){
            order += (' ' + order_sort);
        }
    }

    if (order.length){
        order = ' ORDER BY ' +  order.slice(1);
    }
    return clause + order;
}


/**
 * @author: ares
 * @date: 2018/3/31 22:43
 * @description: 生成mysql插入语句
 * @param {string} table_name 数据库表名
 * @param {array} fields_arr  插入数据的字段
 * @param {array} value_arr   插入的数据(二维数组)
 * @returns {string}
 */
exports.buildInsertStr = function buildInsertStr(table_name,fields_arr,value_arr) {
    var ins = 'INSERT INTO',
        tab_name = ' ' + table_name + ' ',
        fields = '',
        val = '',
        temp_val = '';

    for (var i = 0;i < fields_arr.length;i++){
        fields += (',' + fields_arr[i]);
    }
    fields = '(' + fields.slice(1) + ')';

    for (var j = 0;j < value_arr.length;j++){
        var temp = '';
        for (var k = 0;k < value_arr[j].length;k++){
            var temp_v = value_arr[j][k];
            if (typeof temp_v == 'string'){
                temp_v = '"' + temp_v + '"';
            }
            temp += (',' + temp_v);
        }
        temp_val = '(' + temp.slice(1) + ')';
        val += (',' + temp_val);
    }
    val = ' VALUE ' + val.slice(1);
    return ins + tab_name + fields + val;
}

/**
 * @author: ares
 * @date: 2018/4/1 11:30
 * @description: 生成mysql删除语句
 * @param {string} table_name     数据库表名
 * @param {array} where_clause     where条件,对象元素类型的数组,对象元素{field:'字段名',operator:'操作符',value:'条件值',and_or:'AND | OR'}
 * @param {object} order_clause    排序条件,{fields:[],sort:'ASC | DESC'}
 * @param {boolean} binary        是否区分大小写
 * @returns {string}
 */
exports.buildDeleteStr = function buildDeleteStr(table_name,where_clause,order_clause,binary) {
    var del = 'DELETE FROM',
        tab_name = ' ' + table_name,

        clause = buildSubjoinClause(where_clause,order_clause,binary);

    return del + tab_name + clause;
}

/**
 * @author: ares
 * @date: 2018/4/1 13:09
 * @description: 生成mysql更新语句
 * @param {string} table_name 数据库表名
 * @param {array} option_arr       更新细节,对象元素类型的数组,对象元素{field:'字段名',value:'新值'}
 * @param {array} where_clause     where条件,对象元素类型的数组,对象元素{field:'字段名',operator:'操作符',value:'条件值',and_or:'AND | OR'}
 * @param {object} order_clause    排序条件,{fields:[],sort:'ASC | DESC'}
 * @param {boolean} binary         是否区分大小写
 * @returns {string}
 */
exports.buildUpdateStr = function buildUpdateStr(table_name,option_arr,where_clause,order_clause,binary) {
    var tab_name = ' ' + table_name + ' ',
        upda = 'UPDATE' + tab_name + 'SET ',
        option = '',
        clause = buildSubjoinClause(where_clause,order_clause,binary);

    for (var i = 0;i < option_arr.length;i++){
        var field = option_arr[i].field,
            val = option_arr[i].value;
        if (typeof val == 'string') {
            val = '"' + val + '"';
        }
        option += (',' + field +'=' + val);
    }
    option = option.slice(1);

    return upda + option + clause;
}

/**
 * @author: ares
 * @date: 2018/4/1 15:16
 * @description: 生成mysql查询语句
 * @param {string} table_name 数据库表名
 * @param {array | string | null | undefined} fields          查询的字段名,为string时只能是'*'
 * @param {array} where_clause     where条件,对象元素类型的数组,对象元素{field:'字段名',operator:'操作符',value:'条件值',and_or:'AND | OR'}
 * @param {object} order_clause    排序条件,{fields:[],sort:'ASC | DESC'}
 * @param {boolean} binary        是否区分大小写
 * @param {number} limit                    查询数据条数限制
 * @param {number} offset                   查询偏移量
 * @returns {string}
 */
exports.buildSelectStr = function buildSelectStr(table_name,fields,where_clause,order_clause,binary,limit,offset) {
    var tab_name = ' ' + table_name + ' ',
        field_name = '',
        clause = buildSubjoinClause(where_clause,order_clause,binary),
        limit_num = (limit && typeof limit == 'number') ? limit : 0,
        offset_num = (offset && typeof offset == 'number') ? offset : 0,
        limit_clause = '';

    if(!fields || fields == '*'){
        field_name = '*';
    }else{
        for (var i = 0;i < fields.length;i++){
            field_name += (',' + fields[i]);
        }
        field_name = field_name.slice(1);
    }

    if (limit_num && offset_num){
        limit_clause = ' LIMIT ' + offset_num + ',' + limit_num;
    }else if(limit_num && !offset_num){
        limit_clause = ' LIMIT ' + limit_num;
    }

    return 'SELECT ' + field_name + ' FROM' + tab_name + clause + limit_clause;
}

/**
 * @author: ares
 * @date: 2018/4/1 11:07
 * @description: mysql增删改查
 * @param {string} sql    SQL语句
 * @param {function} callback
 */
exports.query = function query(sql){
    return new Promise(function (resolve, reject) {
        pool.getConnection(function(err,conn){
            if (err){
                console.log(err)
                return;
            }
            conn.query(sql, function (sqlerr,rows) {
                if (!sqlerr){
                    resolve(rows);
                }else {
                    reject(sqlerr);
                }
                conn.release();
            });
        });
    })
}








